// sql.js 定义sql语句
module.exports = {
    users: {
        login: 'select * from users where username=? and password=?;',
        login2: "select * from admin where adname=? and adword=?;",
        reg: 'insert into users values(null,?,?,?);',
        userByname: 'select * from users where username=?;' // 根据用户名查用户是否存在
    },
    goods: {
        goodsByCate: 'select a.*,b.* from goods a,categories b where a.cid=b.cid and a.cid=? and shelves="上架";', //分类id(每大类的所属商品) 并且确保查询只返回特定分类下的商品 并且添加条件去展示上架的商品
        goodsById: 'select * from goods where gid=?;', //商品id(商品所属的大类)  
        nocleargoods: 'SELECT * FROM goods WHERE name LIKE ?;',//like模糊匹配关键字 ？占位符
        brands: 'SELECT * FROM brands;',
    },
    carts: {
        add: 'insert into carts values(null,?,?,?);',
        cartsByUG: 'select * from carts where uid=? and gid=?;',
        addNum: 'update carts set num=num+? where uid=? and gid=?;',
        cartsBU: 'select * from carts,goods where goods.gid=carts.gid and uid=?;', //获取购物车信息和商品信息
        upNum: 'update carts set num=? where cartid=?;',
        del: 'delete from carts where cartid=?;',
        getCount: 'select count(*) as cnt from carts where uid=?;',
        selectdel: 'DELETE FROM carts WHERE cartid=?;' //页面跳转后，删除购物车选中商品
    },
    accounts: {
        addOrder: 'INSERT INTO ordersid (ordernum, uid, ordertime, orderstatus, ordertotal, pricestatus, sendoutGoods,ReceivingStatus) VALUES (?, ?, ?, ?, ?, ?, ?,?);',
        addOrderDetail: 'INSERT INTO ordersdetail (orderid, gid, num,addressid) VALUES (?, ?, ? ,?);',
        priceStatus: "update ordersid set pricestatus=? where pricestatus='未支付' and orderid=?;", //将未支付状态更新为已支付状态
        addRess: 'insert into addresses values(null,?,?,?,?);',
        sele: "SELECT o.ordernum,o.orderid, o.pricestatus, o.ordertime, o.uid, d.gid, g.img,g.price,g.name,d.num,o.ordertotal,o.sendoutGoods,o.ReceivingStatus FROM ordersid AS o JOIN ordersdetail AS d ON o.orderid = d.orderid JOIN goods AS g ON d.gid = g.gid WHERE o.pricestatus = '已支付' AND o.uid = ? ORDER BY o.ordertime DESC;",
        // sele:"SELECT * FROM orders JOIN goods ON orders.gid = goods.gid WHERE orders.pricestatus='已支付' AND orders.uid=? ORDER BY orders.ordertime DESC;", //JOIN关键字来连接orders表和goods表，并通过ON关键字指定了它们之间的关联条件（即orders.gid = goods.gid）。where指定查询条件并且根据时间来降序排序。
        addid: 'select * from addresses where uid=?;',
        adddel: 'delete from addresses where addressid=?;',
        ReceivingStatus: "update ordersid set ReceivingStatus='收货成功' where orderid=?",//订单发货
    },
    admin: {
        showgoods: 'select * from goods ORDER BY goods.gid DESC;',
        addgoods: 'insert into goods values(null,?,?,?,?,?,?,?,?);',
        editgoods: 'update goods set img=?,name=?,totalnum=?,price=?,msg=?,inventory=?,cid=? where gid=?;',
        Offshelf: 'update goods set shelves="下架" where gid=?', //下架 
        Onshelf: 'update goods set shelves="上架" where gid=?', //上架
        showusers: 'select * from users join addresses on users.uid=addresses.uid WHERE addresses.addressid=(SELECT MAX(addressid) FROM addresses WHERE addresses.uid = users.uid) ORDER BY users.uid DESC;',//子查询:查每个uid最新地址。主查询通过 JOIN 连接 users 和 addresses表
        // GROUP_CONCAT聚合函数(将一组值连接成一个字符串。默认情况下，这些值会用逗号分隔。)
        // 根据订单 ID 聚合商品信息。通过聚合函数来将商品信息合并为一行，从而确保每个 orderid 只出现一次。
        showtrueorder: `SELECT 
        ordersid.orderid,
        ordersid.ordernum,
        users.nickname,
        GROUP_CONCAT(goods.name ORDER BY goods.gid) AS goods_names,
        ordersid.ordertotal,
        ordersid.ordertime,
        ordersid.sendoutGoods,
        addresses.name,
        addresses.phone,
        addresses.address
    FROM 
        ordersid
    JOIN 
        ordersdetail ON ordersid.orderid = ordersdetail.orderid
    JOIN 
        users ON ordersid.uid = users.uid
    JOIN 
        goods ON ordersdetail.gid = goods.gid
    JOIN 
        addresses ON ordersdetail.addressid = addresses.addressid
    WHERE 
        ordersid.pricestatus = '已支付'
    GROUP BY 
        ordersid.orderid,ordersid.ordernum,users.nickname, ordersid.ordertotal, ordersid.ordertime,ordersid.sendoutGoods,addresses.name,addresses.phone,addresses.address`, 
    //join(连接)、on(指定连接条件)
        showfalseorder: `SELECT 
    ordersid.orderid,
    ordersid.ordernum,
        users.nickname,
        GROUP_CONCAT(goods.name ORDER BY goods.gid) AS goods_names,
        ordersid.ordertotal,
        ordersid.ordertime,
        ordersid.pricestatus,
        addresses.phone
    FROM 
        ordersid
    JOIN 
        ordersdetail ON ordersid.orderid = ordersdetail.orderid
    JOIN 
        users ON ordersid.uid = users.uid
    JOIN 
        goods ON ordersdetail.gid = goods.gid
    JOIN 
        addresses ON ordersdetail.addressid = addresses.addressid
    WHERE 
        ordersid.pricestatus = '未支付'
        GROUP BY 
        ordersid.orderid,ordersid.ordernum,users.nickname, ordersid.ordertotal, ordersid.ordertime, ordersid.pricestatus,addresses.phone;`, //根据订单 ID 聚合商品信息。 GROUP_CONCAT 聚合函数来将商品信息合并为一行，从而确保每个 orderid 只出现一次。

        ConfirmShipment: "update ordersid set sendoutGoods='已发货',ReceivingStatus='点击收货' where orderid=?",//订单发货
        yizhifu: 'select count(*) from ordersid where pricestatus="已支付";', //已支付订单数统计
        weizhifu: 'select count(*) from ordersid where pricestatus="未支付";', //未支付订单数统计
        userstatistics: 'select count(*) from users;', //用户数量统计
        salestatistics: 'select sum(ordertotal) from ordersid where pricestatus="已支付";', //销售额统计
        // 条件计数法 CASE(条件判断)、when cid = 1(当cid值等于1时)、then 1(满足条件时返回 1)、end(CASE语句结束)
        echone: `SELECT
        COUNT(CASE WHEN cid = 1 THEN 1 END) AS a,
        COUNT(CASE WHEN cid = 2 THEN 1 END) AS b,
        COUNT(CASE WHEN cid = 3 THEN 1 END) AS c,
        COUNT(CASE WHEN cid = 4 THEN 1 END) AS d
    FROM goods;`,  //商城各分类商品数量
        echtwo: `SELECT g.cid, COUNT(od.gid) AS total
    FROM goods g
    LEFT JOIN ordersdetail od ON g.gid = od.gid
    WHERE g.cid IN (1, 2, 3, 4)
    GROUP BY g.cid
    ORDER BY g.cid;`, //订单发货数 echarts   
    // LEFT JOIN(将goods表与ordersdetail表进行左连接),统计goods表中每个类别的商品cid在ordersdetail表中出现的次数。即使goods表中某个类别cid的商品没有出现在ordersdetail表中，也希望该类别的信息能够显示出来，并且计数为 0。
    //GROUP BY分组、ORDER BY排序
        echthree: `SELECT 
        COUNT(*) AS sendout
    FROM ordersid
    WHERE sendoutGoods = '已发货'
    UNION ALL
    SELECT 
        COUNT(*) AS sendout
    FROM ordersid
    WHERE sendoutGoods = '未发货' and pricestatus='已支付';`, //下单的各分类商品数量 echarts
    }//UNION ALL:将两个或多个SELECT语句的结果集合并成一个结果集
}
